﻿/*
 * 
 *	This is a static class of DB-related utility functions (the general core logic of dynamic pages).
 *	All createX/deleteX functions return nothing on success, and throw an error on failure.
 *	User permission validation is only done in getX functions where permissions are an essential part of the purpose of the function.
 *	In all other functions, permissions are assumed to have been previously validated.
 *	
 *	TODO: lots
 * 
 */

using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using ResearchRepository.Models;

namespace WebControlsTest
{
    public static class RRDatabaseManager
    {
		// connection used for lifetime of RRDatabaseManager/application
			// conn is opened and closed on each call to ExecuteNonQuery and ExecuteScalar
			// conn is only opened by ExecuteReader and then closed by GetData after all data is read from the reader (conn must be open to read)
			// if GetData is not used to read the results of ExecuteReader, then it is the reading function's responsibility to close conn
		public static readonly MySqlConnection conn = new MySqlConnection(GLOBALS.dbConnStr);
		
		/*
        public void Dispose()
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
			conn.Dispose();
        }
		*/

		public static string ConnectionString
		{
			get {return conn.ConnectionString;}
			set	{conn.ConnectionString = value;}
		}

		public static int ExecuteNonQuery(string sqlStr)
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				return cmd.ExecuteNonQuery();
			}
			catch (Exception x)
			{
				throw x;
			}
			finally
			{
				conn.Close();
			}
		}
		public static object ExecuteScalar(string sqlStr)
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				return cmd.ExecuteScalar();
			}
			catch (Exception x)
			{
				throw x;
			}
			finally
			{
				conn.Close();
			}
		}
		public static MySqlDataReader ExecuteReader(string sqlStr)
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				return cmd.ExecuteReader();
			}
			catch (Exception x)
			{
				throw x;
			}
			finally
			{
				// connection must remain open to read results from reader
				//conn.Close();
			}
		}
////////
////////	universities
////////
		// returns nothing on success
		public static void createUniversity(RRUniversity univ)
		{
			string sqlStr = "INSERT INTO universities VALUES (DEFAULT, '" + univ.UnivName + "')";
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Could not create university '" + univ.UnivName + "'.");
			}
		}
		// returns a university object (or null)
		public static RRUniversity getUniversity(string univName)
		{
			string sqlStr = "SELECT * FROM universities WHERE univName='" + univName + "'";
			IEnumerable<RRUniversity> res = GetData(ExecuteReader(sqlStr), RRUniversity.construct);
			return res.DefaultIfEmpty(null).ElementAt(0);
		}
		// get list of all universities
		public static IEnumerable<RRUniversity> getUniversityList()
		{
			string sqlStr = "SELECT * FROM universities ORDER BY univName";
			return GetData(ExecuteReader(sqlStr), RRUniversity.construct);
		}
////////
////////	universityDepartments
////////
		// returns nothing on success
		public static void createDepartment(RRUniversityDepartment dept)
		{
			string sqlStr = "INSERT INTO universityDepartments VALUES (DEFAULT, " + dept.UnivId + ", '" + dept.DeptName + "')";
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create university department.");
			}
		}
		// returns a universityDepartment object (or null)
		public static RRUniversityDepartment getDepartment(int univID, string deptName)
		{
			string sqlStr = "SELECT * FROM universityDepartments WHERE univID=" + univID + " AND deptName='" + deptName + "'";
			IEnumerable<RRUniversityDepartment> res = GetData(ExecuteReader(sqlStr), RRUniversityDepartment.construct);
			return res.DefaultIfEmpty(null).ElementAt(0);
		}
		// get list of departments for a given university
        public static IEnumerable<RRUniversityDepartment> getDepartmentList(int univID)
        {
			//TODO repackage as tree structure
            string sqlStr = "SELECT * FROM universityDepartments WHERE univID=" + univID + " AND deptName!='' ORDER BY deptName";
            return GetData(ExecuteReader(sqlStr), RRUniversityDepartment.construct);
        }
////////
////////	disciplines
////////
        // get list of disciplines
		//TODO: iteratively populate a hierarchical tree, since the 'discipline' table is a recursive relation
        public static IEnumerable<RRDiscipline> getDisciplineList()
        {
            string sqlStr = "SELECT * FROM disciplines ORDER BY DiscName";
            return GetData(ExecuteReader(sqlStr), RRDiscipline.construct);
        }
////////
////////	users
////////
		//
		public static void createUser(RRUser user)
		{
			string sqlStr = String.Format("INSERT INTO users VALUES (DEFAULT, '{0}', PASSWORD('{1}'), {2}, '{3}', '{4}', '{5}', '{6}', '{7}')",
											user.UserName, user.UserPass, user.DeptID == 0 ? "NULL" : ""+user.DeptID,
											user.FirstName, user.MiddleName, user.LastName, user.PreTitles, user.PostTitles);
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create user.");
			}
		}
		// validate credentials or just check for existing user (if password == null)
			// calling function is responsible for handling exceptions
		public static RRUser getUser(string username, string password = null)
		{
			string sqlStr = "SELECT *, "
								+ " (SELECT univID FROM universityDepartments d WHERE d.deptID=u.deptID) AS univID"	// get univID of user's department (returns NULL is u.deptID is NULL)
							+ " FROM users u"
							+ " WHERE u.userName='" + username + "'"
							+ (password == null ? "" : " AND u.userPass=PASSWORD('" + password + "')");
			return GetData(ExecuteReader(sqlStr), RRUser.construct).DefaultIfEmpty(null).ElementAt(0);
		}
		// determine if a user has admin permissions on at least 1 file
		public static bool userIsAdmin(int userID, bool isRepoAdmin = false, bool isProjAdmin = false)
		{
			string sqlStr = "SELECT COUNT(*) FROM users u, permissions p, permissionTypes pt"
											+ " WHERE " + userID + "=u.userID AND u.userID=p.userID AND p.permID=pt.permID AND pt.permName='admin'";
			if (isRepoAdmin && isProjAdmin)
			{
				sqlStr += " AND (p.fileID=-1 OR p.fileID IN (SELECT rootDirID FROM projects))";
			}
			else if (isRepoAdmin)
			{
				sqlStr += " AND p.fileID=-1";
			}
			else if (isProjAdmin)
			{
				sqlStr += " AND p.fileID IN (SELECT rootDirID FROM projects)";
			}

			return ((long) ExecuteScalar(sqlStr)) > 0;
		}
		//
        public static void updateAccountInfo(int userID, string newEmail, string newPass, string newPreTitles, string newFirst, string newMiddle, string newLast, string newPostTitles, string dept)
        {
            string[] commas = new string[6];

            // create the sql query--this will either update to the new value or keep the current value for each column
            // if null or an empty string is passed as one of the parameters string newPass = String.Format("userName = PASSWORD('{0}')", NewPasswordConfirm.Text);
            string sqlStr = "UPDATE users SET "
                              + "userName = " +
                              (!String.IsNullOrEmpty(newEmail)
                                   ? String.Format("'{0}'", newEmail)
                                   : "userName")
                              + ", userPass = " +
                              (!String.IsNullOrEmpty(newPass)
                                   ? String.Format("PASSWORD('{0}')", newPass)
                                   : "userPass")
                              + ", preTitles = " +
                              (!String.IsNullOrEmpty(newPreTitles)
                                   ? String.Format("'{0}'", newPreTitles)
                                   : "preTitles")
                              + ", firstName = " +
                              (!String.IsNullOrEmpty(newFirst)
                                   ? String.Format("'{0}'", newFirst)
                                   : "firstName")
                              + ", middleName = " +
                              (!String.IsNullOrEmpty(newMiddle)
                                   ? String.Format("'{0}'", newMiddle)
                                   : "middleName")
                              + ", lastName = " +
                              (!String.IsNullOrEmpty(newLast)
                                   ? String.Format("'{0}'", newLast)
                                   : "lastName")
                              + ", postTitles = " +
                              (!String.IsNullOrEmpty(newPostTitles)
                                   ? String.Format("'{0}'", newPostTitles)
                                   : "postTitles")
                              + ", deptID = " +
                              (!String.IsNullOrEmpty(dept)
                                   ? String.Format("{0}", dept)
                                   : "deptID")
                              + String.Format(" WHERE userID = {0}", userID);
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to update user account data.");
			}
        }
////////
////////	files
////////
		//
		public static bool fileExists(int superDirID, string fileName)
		{
			string sqlStr = "SELECT COUNT(*) FROM files where superDirID=" + superDirID + " AND fileName='" + fileName + "'";
			return ((long) ExecuteScalar(sqlStr)) > 0;
		}
		//
		public static void createFile(RRFile newFile)
		{
			// insert new file into DB
			string userID = newFile.CreatorId != RRUser.GuestUserID ? ""+newFile.CreatorId : "(SELECT userID FROM users WHERE userName='guest')";
			string sqlStr = String.Format("INSERT INTO files VALUES (DEFAULT, {0}, '{1}', {2}, NOW(), {3}, NOW(), {4}, '{5}')",
											newFile.SuperDirID, newFile.FileName, newFile.IsDir ? 1 : 0,
											userID, userID, escapeString(newFile.Description));
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create file.");
			}
		}
		//
		public static void deleteFile(RRFile file)
		{
			string sqlStr = String.Format("DELETE FROM files WHERE superDirID={0} AND filename='{1}' AND creatorID={2}",
											file.SuperDir.FileID, file.FileName, file.CreatorId);
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to delete file.");
			}
		}
		// simple file fetch using fileID and running no validation
		public static RRFile getFile(int fileID)
		{
			string sqlStr = "SELECT f.*, c.userName AS creatorName, m.userName as modifierName,"
									+ " 'none' AS userPermName, 'none' AS publicPermName"					// force defaults to make RRFile.construct happy
							+ " FROM files f, users c, users m"
							+ " WHERE f.fileID=" + fileID
									+ " AND c.userID=f.creatorID AND m.userID=f.modifierID";				// get usernames for creator and last modifier;
			return GetData(ExecuteReader(sqlStr), RRFile.construct).DefaultIfEmpty(null).ElementAt(0);
		}
		// simple file fetch using superDirID and filename and running no validation
		public static RRFile getFile(int superDirID, string fileName)
		{
			string sqlStr = "SELECT f.*, c.userName AS creatorName, m.userName as modifierName,"
									+ " 'none' AS userPermName, 'none' AS publicPermName"					// force defaults to make RRFile.construct happy
							+ " FROM files f, users c, users m"
							+ " WHERE f.superDirID=" + superDirID + " AND f.fileName='" + fileName + "'"
									+ " AND c.userID=f.creatorID AND m.userID=f.modifierID";				// get usernames for creator and last modifier;
			return GetData(ExecuteReader(sqlStr), RRFile.construct).DefaultIfEmpty(null).ElementAt(0);
		}
		// get updated data for file and user; new version of file is not connected to file.superDir
			// if file is no longer accessible (deleted/moved/permission revoked), then return null
			// else return new file data, with sub files populated down to the depth passed (0 = just get data for given file)
		public static RRFile getFileData(RRFile file, int userID, int depth = 0)
		{
			RRFile superDir = RRFile.EmptyRoot;

			// if file is not repo root, then get and re-validate old parent dir permissions (else: use default empty root as super for base permissions)
			if (file.FileID != superDir.FileID)
			{
				// recursively get up-to-date data for super
				superDir = getFileData(file.SuperDir, userID);
				// if parent is unreachable, then so is this file
				if (superDir == null)
				{
					return null;
				}
			}
			// build MySQL query to update starting file
			string sqlStr = "SELECT f.*, c.userName AS creatorName, m.userName as modifierName, "
											+ (userID==RRUser.GuestUserID ? "NULL"								// if guest user, then don't look for user-specific permissions
												: superDir.UserPermission==RRPermissionType.None ? "'none'"		// elseif registered user has 'none' permission on parent dir, then all children have 'none' permission
												: superDir.UserPermission==RRPermissionType.Admin ? "'admin'"	// elseif registered user has 'admin' permission on parent dir, then all children have 'admin' permission
																												// else, find registered user's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=" + userID + " AND p.fileID=f.fileID)"														// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=" + userID + " AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + superDir.UserPermission.ToString() + "' END)")																						// else, inherit permission from parent
											+ " AS userPermName, "
											+ (superDir.PublicPermission==RRPermissionType.None ? "'none'"		// if public has 'none' permission on parent dir, then all children have 'none' permission
												: superDir.PublicPermission==RRPermissionType.Admin ? "'admin'"	// elseif public has 'admin' permission on parent dir, then all children have 'admin' permission (this is obviously not suggested, but allowable)
																												// else, find public's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID)"													// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + superDir.PublicPermission.ToString() + "' END)")																														// else, inherit permission from parent
											+ " AS publicPermName"
									+ " FROM files f, users c, users m"
									+ " WHERE f.fileID=" + file.FileID + " AND f.superDirID=" + superDir.FileID	// just get starting file and only of it's in the same place
										+ " AND c.userID=f.creatorID AND m.userID=f.modifierID"					// get usernames for creator and last modifier
									+ " HAVING userPermName!='none' OR publicPermName!='none'"					// a file is only reachable if user has some permission
									+ " ORDER BY f.fileName";
			// run query
			List<RRFile> res = new List<RRFile>(GetData(ExecuteReader(sqlStr), RRFile.construct));
			// if empty result, then file is no longer accessible
			if (res.Count == 0)
			{
				return null;
			}
			// else get updated file data
			RRFile newFile = res.ElementAt(0);

			// populate children (does nothing if not a directory)
			getSubFileData(newFile, userID, depth);

			return newFile;
		}
		// recursively populate directories down to depth given
			// since this is private, it is assumed that all necessary validation has been done and first file permissions are up-to-date
		private static void getSubFileData(RRFile file, int userID, int depth)
		{
			// if not a directory or recursive base case, then do nothing
			if (!file.IsDir || depth < 1)
			{
				return;
			}
			// build MySQL query to get sub files
			string sqlStr = "SELECT f.*, c.userName AS creatorName, m.userName as modifierName, "
											+ (userID==RRUser.GuestUserID ? "NULL"							// if guest user, then don't look for user-specific permissions
												: file.UserPermission==RRPermissionType.None ? "'none'"		// elseif registered user has 'none' permission on parent dir, then all children have 'none' permission
												: file.UserPermission==RRPermissionType.Admin ? "'admin'"	// elseif registered user has 'admin' permission on parent dir, then all children have 'admin' permission
																											// else, find registered user's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=" + userID + " AND p.fileID=f.fileID)"														// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=" + userID + " AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + file.UserPermission.ToString() + "' END)")																							// else, inherit permission from parent
											+ " AS userPermName, "
											+ (file.PublicPermission==RRPermissionType.None ? "'none'"		// if public has 'none' permission on parent dir, then all children have 'none' permission
												: file.PublicPermission==RRPermissionType.Admin ? "'admin'"	// elseif public has 'admin' permission on parent dir, then all children have 'admin' permission (this is obviously not suggested, but allowable)
																											// else, find public's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID)"													// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + file.PublicPermission.ToString() + "' END)")																															// else, inherit permission from parent
											+ " AS publicPermName"
									+ " FROM files f, users c, users m"
									+ " WHERE f.fileID!=f.superDirID AND f.superDirID=" + file.FileID		// get all sub files (ignore root which is its own sub/super)
										+ " AND c.userID=f.creatorID AND m.userID=f.modifierID"				// get usernames for creator and last modifier
									+ " HAVING userPermName!='none' OR publicPermName!='none'"				// a file is only reachable if user has some permission
									+ " ORDER BY f.fileName";
			// run query
			List<RRFile> res = new List<RRFile>(GetData(ExecuteReader(sqlStr), RRFile.construct));
			// add each result file to tree and recursively populate it
			for (int i = 0; i < res.Count; i++)
			{
				file.SubFiles.Add(res[i]);		// parent -> child
				res[i].SuperDir = file;			// child -> parent
				getSubFileData(res[i], userID, depth-1);
			}
		}
////////
////////	projects
////////
		//
		public static void createProject (RRProject proj)
		{
			string sqlStr = String.Format("INSERT INTO projects VALUES (DEFAULT, '{0}', {1}, {2}, {3})",
											proj.ProjName, proj.RootDirId,
											proj.DiscId == 0 ? "NULL" : "" + proj.DiscId,
											proj.ResId == 0 ? "NULL" : "" + proj.ResId);
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create project.");
			}
		}
		//
		public static RRProject getProject(string projName)
		{
			string sqlStr = "SELECT * FROM projects WHERE projName='" + projName + "'";
			return GetData(ExecuteReader(sqlStr), RRProject.construct).DefaultIfEmpty(null).ElementAt(0);
		}
		// get project which contains file
		public static RRProject getProject(RRFile file)
		{
			// no projects are associated with the repo root
			if (file.FileID == RRFile.EmptyRoot.FileID)
			{
				return null;
			}
			// iterate up the file tree to find the project's root dir; all project roots are immediate children of the repo root
			RRFile temp = file;
			while (temp.SuperDirID != RRFile.EmptyRoot.FileID)
			{
				temp = getFile(temp.SuperDirID);
			}
			string sqlStr = "SELECT * FROM projects WHERE rootDirID=" + temp.FileID;
			return GetData(ExecuteReader(sqlStr), RRProject.construct).DefaultIfEmpty(null).ElementAt(0);
		}
////////
////////	permissions
////////
		//
		public static void createPermissions(int userID, int fileID, RRPermissionType perm)
		{
			string sqlStr = String.Format("INSERT INTO permissions VALUES ({0}, {1}, (SELECT permID FROM permissionTypes WHERE permName='{2}'))",
											fileID, userID != RRUser.GuestUserID ? ""+userID : "(SELECT userID FROM users WHERE userName='guest')", perm.ToString().ToLower());
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create permissions.");
			}
		}
		//
		public static void createPermissions(int userID, int superDirID, string filename, RRPermissionType perm)
		{
			string sqlStr = "INSERT INTO permissions VALUES ((SELECT fileID FROM files WHERE superDirID=" + superDirID + " AND fileName='" + filename + "'),"
															+ (userID != RRUser.GuestUserID ? userID+"," : "(SELECT userID FROM users WHERE userName='guest'),")
															+ " (SELECT permID FROM permissionTypes WHERE permName='" + perm.ToString().ToLower() + "'))";
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create permissions.");
			}
		}
////////
////////	requests
////////
		// insert a new row into the 'requests' table
			// all string properties of request have previously been sanitized against SQL-insertion by submission page EXCEPT the 'note' field
		public static void createRequest(RRRequest request)
		{
			string cols = "", values = "";
			switch (request.ReqType)
			{
				case RRRequestType.NewUser:
					//TODO: check for unique newUserEmail (can't register the same email twice)
					cols = ", newUserEmail, newUserPreTitles, newUserFirstName, newUserMiddleName, newUserLastName, newUserPostTitles,"
							+ " univID, newUnivName, deptID, newDeptName";
					values = ", '" + request.NewUserEmail + "', '" + request.NewUserPreTitles + "', '" + request.NewUserFirstName + "'"
								+ ", '" + request.NewUserMiddleName + "', '" + request.NewUserLastName + "', '" + request.NewUserPostTitles + "'"
								+ ", " + (request.UnivID == 0 ? "NULL" : ""+request.UnivID)
								+ ", " + (request.NewUnivName == null ? "NULL" : "'"+request.NewUnivName+"'")
								+ ", " + (request.DeptID == 0 ? "NULL" : ""+request.DeptID)
								+ ", " + (request.NewDeptName == null ? "NULL" : "'"+request.NewDeptName+"'");
					break;
				case RRRequestType.NewProj:
					cols = ", newProjName, discID, permID";
					values = ", '" + request.NewProjName + "', " + request.DiscID
							+ ", (SELECT permID FROM permissionTypes WHERE permName='" + request.PermType.ToString().ToLower() + "')";
					break;
				case RRRequestType.NewPerm:
					cols = ", permID, fileID";
					values = ", (SELECT permID FROM permissionTypes WHERE permName='" + request.PermType.ToString().ToLower() + "'), " + request.FileID;
					break;
				case RRRequestType.File:
					cols = ", fileID";
					values = ", " + request.FileID;
					break;
                case RRRequestType.NewUnivOrDept:
			        cols = ", newUnivName, newDeptName";
			        values = ", '" + request.NewUnivName + "', '" + request.NewDeptName + "'";
			        break;
				case RRRequestType.General:
					//TODO??
					break;
			}
			string sqlStr = "INSERT INTO requests (reqTypeID, userID, projID, note, statID" + cols + ") VALUES ("
											/* reqTypeID */	+ " (SELECT reqTypeID FROM requestTypes WHERE reqTypeName='" + request.ReqType.ToString().ToLower() + "'),"
											   /* userID */	+ (request.UserID!=RRUser.GuestUserID ? request.UserID + "," : " (SELECT userID FROM users WHERE userName='guest'),")
											   /* projID */	+ (request.ProjID == 0 ? "NULL," : request.ProjID + ",")
												 /* note */	+ " '" + escapeString(request.Note) + "',"	/* sanitize note: escape existing escape chars, then escape single-quotes */
											   /* statID */	+ " (SELECT statID FROM statusTypes WHERE statName='pending')"
															+ values
															+ ")";
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to create request.");
			}
		}
		// update request on status change
			// assumes reqID, reqTypeID, dateSubmitted, and all strings (newUserX's, newUnivName, newDeptName, and newProjName) are constant (for now)
		private static void updateRequest(RRRequest request)
		{
			string sqlStr = "UPDATE requests"
							+ " SET userID=" + request.UserID + ","
									+ (request.ProjID == 0 ? "" : " projID=" + request.ProjID + ",")
									+ " note='" + escapeString(request.Note) + "',"
									+ " statID=(SELECT statID FROM statusTypes WHERE statName='" + request.StatType.ToString().ToLower() + "'),"
									+ " dateAdmined=NOW()"
									+ (request.AdminID == 0 ? "" : ", adminID=" + request.AdminID)
									+ (request.UnivID == 0 ? "" : ", univID=" + request.UnivID)
									+ (request.DeptID == 0 ? "" : ", deptID=" + request.DeptID)
									+ (request.DiscID == 0 ? "" : ", discID=" + request.DiscID)
									+ (request.PermID == 0 ? "" : ", permID=(SELECT permID FROM permissionTypes WHERE permName='" + request.PermType.ToString().ToLower() + "')")
									+ (request.FileID == 0 ? "" : ", fileID=" + request.FileID)
							+ " WHERE reqID=" + request.ReqID;
			if (ExecuteNonQuery(sqlStr) <= 0)
			{
				throw new Exception("Failed to update request #" + request.ReqID);
			}
		}
		// get all requests that are servicable by the user denoted by adminID
		public static IEnumerable<RRRequest> getAdminRequestList(int adminID)
		{
			string sqlStr = "SELECT r.*, rt.reqTypeName, u.userName, s.statName,"
								+ " (SELECT projName FROM projects pj WHERE pj.projID=r.projID) AS projName,"		// get projName of associated project (returns NULL if r.projID is NULL)
								+ " (SELECT permName FROM permissionTypes pe WHERE pe.permID=r.permID) AS permName"	// get permName of requested permission (returns NULL if r.permID is NULL)
							+ " FROM requests r, statusTypes s, requestTypes rt, users u, permissions p, permissionTypes pt"
							+ " WHERE 'pending'=s.statName"					// only get pending requests
									+ " AND s.statID=r.statID"
									+ " AND rt.reqTypeID=r.reqTypeID"		// get rt.reqTypeName
									+ " AND u.userID=r.userID"				// get u.userName
									+ " AND 'admin'=pt.permName"			// check that admin has admin permissions
									+ " AND pt.permID=p.permID"
									+ " AND p.userID=" + adminID
											// if user has 'admin' permissions on repo root (-1), then they are repo admin
											// AND if request has no projID (NULL), then request is for repo admins (so, get it)
									+ " AND ((p.fileID=-1 AND r.projID IS NULL)"	
											// if user has 'admin' permissions on a project root, then they are a project admin (so, get requests associated with that project)
											+ " OR p.fileID IN (SELECT rootDirID FROM projects pj WHERE pj.projID=r.projID))";
			//TODO: get requests for users that only admin a sub-directory of a project
				// requires iteratively finding links between subdir (r.fileID) and superdir(p.fileID)

			return GetData(ExecuteReader(sqlStr), RRRequest.construct);
		}
		// get all requests that have been submitted by user
		public static IEnumerable<RRRequest> getUserRequestList(int userID)
		{
			string sqlStr = "SELECT r.*, rt.reqTypeName, NULL AS userName, s.statName,"								// include "NULL AS userName" to make RRRequest.construct happy
								+ " (SELECT projName FROM projects pj WHERE pj.projID=r.projID) AS projName"		// get projName of associated project (returns NULL if r.projID is NULL)
								+ " (SELECT permName FROM permissionTypes pe WHERE pe.permID=r.permID) AS permName"	// get permName of requested permission (returns NULL if r.permID is NULL)
							+ " FROM requests r, statusTypes s, requestTypes rt"
							+ " WHERE s.statID=r.statID"					// get s.statName
									+ " AND rt.reqTypeID=r.reqTypeID"		// get rt.reqTypeName
									+ " AND r.userID=" + userID;
			return GetData(ExecuteReader(sqlStr), RRRequest.construct);
		}
		// this is currently designed with the intent to handle requests for all request types
			// implemented: NewUser, NewProj, NewPerm, NewUnivOrDept; TODO: General, File
			// request is continuously updated with the ID for the item that was just created to potentially use it at a later stage
			// thus, the order of item creation for complex/compound requests is very important
		public static void acceptRequest(RRRequest request)
		{
			try
			{
				// create new university and/or department
				if (request.ReqType == RRRequestType.NewUser || request.ReqType == RRRequestType.NewUnivOrDept)
				{
					// create new university with default "dummy" department and update request object
					if (request.UnivID == 0 && request.NewUnivName != null)
					{
						createUniversity(new RRUniversity(0, request.NewUnivName));
						request.UnivID = getUniversity(request.NewUnivName).UnivId;
						createDepartment(new RRUniversityDepartment(0, request.UnivID, ""));
					}
					if (request.DeptID == 0)
					{
						// create new department and update request object
						if (request.NewDeptName != null)
						{
							createDepartment(new RRUniversityDepartment(0, request.UnivID, request.NewDeptName));
							request.DeptID = getDepartment(request.UnivID, request.NewDeptName).DeptId;
						}
						// get default "dummy" department
						else if (request.UnivID != 0)
						{
							request.DeptID = getDepartment(request.UnivID, "").DeptId;
						}
					}
				}
				// create new user and update request object
				if (request.ReqType == RRRequestType.NewUser)
				{
					string genPass = generateRandomPassword();	// TODO: just a stub that returns "randomPassword"; to be properly implemented once email notifications are implemented
					createUser(new RRUser(0, request.NewUserEmail, genPass, 0, request.DeptID,
											request.NewUserFirstName, request.NewUserMiddleName, request.NewUserLastName,
											request.NewUserPreTitles, request.NewUserPostTitles));
					request.UserID = getUser(request.NewUserEmail).UserID;
				}
				// create new project (w/ proj root dir)
				else if (request.ReqType == RRRequestType.NewProj)
				{
					// store temp file for potential cleanup
					RRFile rootDir = new RRFile(0, RRFile.EmptyRoot.FileID, request.NewProjName, true,
												new MySqlDateTime(), request.UserID, new MySqlDateTime(), request.UserID,
												"Root directory for " + request.NewProjName);	// use project name as root dir name
					// create root dir in DB and update request object
					createFile(rootDir);
					request.FileID = getFile(rootDir.SuperDirID, rootDir.FileName).FileID;	// for permission creation
					try
					{
						// create root dir on server file system, on failure: rollback DB file creation and throw a new exception
						System.IO.Directory.CreateDirectory(GLOBALS.repoDataRootDir + request.NewProjName);
					}
					catch(Exception x)
					{
						// remove new file entry from DB
						try
						{
							rootDir.FileID = getFile(rootDir.SuperDirID, rootDir.FileName).FileID;
							deleteFile(rootDir);
						}
						catch (Exception x2)
						{
							//TODO how to recover/cleanup/report on DB cleanup fail??
						}
						throw new Exception("Failed to create project root directory on server file system: " + x.Message);
					}
					// create project
						// TODO: last 0 in constructor is for researchType, which was apparently forgetten in the RRRequest class
					createProject(new RRProject(0, request.NewProjName, request.FileID, request.DiscID, 0));
					request.ProjID = getProject(request.NewProjName).ProjId;
					// create public permission for project
					createPermissions(RRUser.GuestUserID, request.FileID, request.PermType);
					// setup admin for permission creation
					request.PermType = RRPermissionType.Admin;
					request.PermID = -1;		// flag permType change for updateRequest()
				}
				// create new permissions
				if (request.ReqType == RRRequestType.NewProj || request.ReqType == RRRequestType.NewPerm)
				{
					createPermissions(request.UserID, request.FileID, request.PermType);
				}
				// finalize request
				request.StatType = RRStatusType.Accepted;
			}
			catch(Exception x)
			{
				request.StatType = RRStatusType.Failed;
				throw x;
			}
			finally
			{
				// always push updated request back to DB
				updateRequest(request);
			}
		}
		//
		public static void rejectRequest(RRRequest request)
		{
			request.StatType = RRStatusType.Rejected;
			updateRequest(request);
		}
////////
////////	util functions
////////
		/// <summary>
        /// Takes an object type which uses a reader and build a class representation of the data
        /// </summary>
        /// <typeparam name="T">The type of object that represents that database data</typeparam>
        /// <param name="reader">The reader object</param>
        /// <param name="buildObject">The object that will be used to hold that reader data</param>
        /// <returns>An enumerable object to represent the reader data</returns>
        private static IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> buildObject)
        {
            try
            {
                if (!reader.IsClosed)
                {
                    while (reader.Read())
                    {
                        yield return buildObject(reader);
                    }
                }
            }
            finally
            {
				reader.Dispose();
				conn.Close();
            }
        }
		// returns a sanitized string (to protect to DB from SQL insertion)
		public static string escapeString(string str)
		{
			return str.Replace("\\", "\\\\").Replace("\'", "\\\'").Replace("\"", "\\\"");
		}
		//TODO implement random string generator after email notifications are implemented
		public static string generateRandomPassword()
		{
			return "randomPassword";
		}
    }
}